using System;
using System.Collections;
using System.Collections.Generic;
using System.Globalization;
using System.Reflection;
using System.Security;
using System.Security.Permissions;
using System.Text;
using DataBroker.Criteria;
using DataBroker.Dao;
using log4net;

namespace DataBroker.Dal
{
    /// <summary>
    /// Summary description for D2BkSql.
    /// </summary>
    /// <remarks>$Id$</remarks>
    internal class D2BkSql
    {
        /// <summary>
        /// Type of object
        /// </summary>
        private string PrimaryTableName = "";
        private Entity _theObject;
        private static readonly ILog Log =
            LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        /// <summary>
        /// The main object from which the D2Bk statements are derived.
        /// </summary>
        public Entity TheObject
        {
            get
            {
                return _theObject;
            }
        }

        /// <summary>
        /// Default constructor.
        /// </summary>
        public D2BkSql()
        {
            D2Bk.d2BkSqlParameters.CurrentField = 1;
        }
        /// <summary>
        /// Constructor that receives a Type value to build the D2Bk Statements.
        /// </summary>
        /// <param name="valueObjectType">Object value.</param>
        public D2BkSql(Type valueObjectType)
            : this()
        {
            _theObject = (Entity)Activator.CreateInstance(valueObjectType);
        }

        /// <summary>
        /// Constructor that receives a Type value to build the D2Bk Statements.
        /// </summary>
        /// <param name="theObject">Object value.</param>
        public D2BkSql(Entity theObject)
            : this()
        {
            _theObject = theObject;
        }

        /// <summary>
        /// Get current entity type.
        /// </summary>
        /// <returns></returns>
        /// <remarks>Return the value type currently set for this object.</remarks>
        private Type GetEntityType()
        {
            return _theObject.GetType();
        }

        /// <summary>
        /// Obtains the database table name corresponding to the
        /// object type. By default it will be the same as the
        /// entity type name.
        /// </summary>
        /// <exception cref="System.Exception">Thrown if no Entity type
        /// has been set and no Table Name.</exception>
        /// <returns>The current database table name.</returns>
        internal String GetPrimaryTable()
        {
            string primaryTable = "";
            if (PrimaryTableName.Length > 0)
                primaryTable = PrimaryTableName;
            if (_theObject != null)
            {
                primaryTable = _theObject.TableName();
            }
            string brackets = D2BkConnection.DBBrackets();
            if (brackets.Length > 0)
                return string.Format("{1}{0}{2}", primaryTable, brackets[0], brackets[1]);
            return primaryTable;
        }




        /// <summary>
        /// Maps a field from the supported object's attributes to a database field.
        /// The Entity can override its own MapField method to allow for creating
        /// expressions, for example. 
        /// </summary>
        /// <param name="fld">Field name.</param>
        /// <returns>Corresponding database field (with table name).</returns>
        private String MapField(String fld)
        {
            if (_theObject.GetType().IsSubclassOf(typeof(Entity)))
            {
                return TheObject.MapField(fld);
            }
            return fld;
        }
        /// <summary>
        /// Gets the fields to be used in a search, if none was specified. 
        /// </summary>
        /// <returns></returns>
        internal List<string> GetPrecacheFieldsList()
        {
            string[] precacheFields = GetPrecacheFields();
            List<string> precache = new List<string>();
            for (int i = 0; i < precacheFields.Length; i++)
                precache.Add(precacheFields[i]);
            return precache;
        }
        /// <summary>
        /// Get precache fields. Return all in entity.
        /// </summary>
        /// <returns>All fields in an entity, if they are not marked as not in table.</returns>
        internal string[] GetPrecacheFields()
        {
            return GetPrecacheFields(GetEntityType());
        }

        /// <summary>
        /// Get precache fields. Return all in entity.
        /// </summary>
        /// <returns></returns>
        internal static string[] GetPrecacheFields(Type entityType)
        {
            List<string> fieldList = new List<string>();
            foreach (PropertyInfo prop in (entityType.GetProperties()))
            {
                if (!D2Bk.IsNotInTable(prop))
                {
                    fieldList.Add(prop.Name);
                }
            }
            return fieldList.ToArray();
        }

        /// <summary>
        /// Get precache fields. Return all in entity.
        /// </summary>
        /// <returns></returns>
        internal static List<string> GetPrecacheFieldList(object entity)
        {
            return GetPrecacheFieldList(entity, true);
        }

        /// <summary>
        /// Get precache fields. Return all in entity.
        /// </summary>
        /// <returns></returns>
        internal static List<string> GetPrecacheFieldList(object entity, List<string> precache)
        {
            return GetPrecacheFieldList(entity, ((Entity)entity).TableAlias(), true, precache);
        }

        /// <summary>
        /// Get precache fields. Return all in entity.
        /// </summary>
        /// <returns></returns>
        internal static List<string> GetPrecacheFieldList(object entity, bool includeColumnValues)
        {
            return GetPrecacheFieldList(entity, ((Entity)entity).TableAlias(), includeColumnValues, null);
        }
        /// <summary>
        /// Get all precache fields for entity. 
        /// </summary>
        /// <param name="entity">Entity object to retrieve list of fields.</param>
        /// <param name="alias">Name to which the table is referred to. This alias
        /// is added to the column name to circumvent a limitation of ADO.NET that
        /// does not allow for retrieving columns with the same name - it does not 
        /// support naming to which table the column belongs. This might change on ADO.NET 2.0.</param>
        /// <returns></returns>
        internal static List<string> GetPrecacheFieldList(object entity, string alias)
        {
            return GetPrecacheFieldList(entity, alias, true, null);
        }
        /// <summary>
        /// Get all precache fields for entity. 
        /// </summary>
        /// <param name="entity">Entity object to retrieve list of fields.</param>
        /// <param name="alias">Name to which the table is referred to. This alias
        /// is added to the column name to circumvent a limitation of ADO.NET that
        /// does not allow for retrieving columns with the same name - it does not 
        /// support naming to which table the column belongs. This might change on ADO.NET 2.0.</param>
        /// <param name="includeColumnValues">indicates is column values are to be included.</param>
        /// <param name="precache"></param>
        /// <returns></returns>
        internal static List<string> GetPrecacheFieldList(object entity, string alias, bool includeColumnValues, List<string> precache)
        {
            List<string> fieldList = new List<string>();
            string bracketedAlias = alias;
            string brackets = D2BkConnection.DBBrackets();
            if (brackets.Length > 1)
                bracketedAlias = string.Format("{1}{0}{2}", alias, brackets[0], brackets[1]);
            if (precache == null)
            {
                foreach (PropertyInfo prop in (entity.GetType().GetProperties()))
                {
                    AddPropertyFromPrecache(bracketedAlias, alias, precache, entity, prop, fieldList);
                }
            }
            else
            {
                foreach (string field in precache)
                {
                    PropertyInfo prop =
                        (entity.GetType().GetProperty(field,
                                                      BindingFlags.Instance | BindingFlags.IgnoreCase |
                                                      BindingFlags.GetProperty | BindingFlags.Public));
                    if (prop != null)
                    {
                        if (!D2Bk.IsNotInTable(prop))
                        {
                            fieldList.Add(bracketedAlias + "." + prop.Name + " AS " + alias + prop.Name);
                        }
                        else
                        {
                            AddPropertyFromPrecache(bracketedAlias, alias, precache, entity, prop, fieldList);
                        }
                    }
                }
            }
            return fieldList;
        }

        private static void AddPropertyFromPrecache(string bracketedAlias, string alias, List<string> precache, object entity, PropertyInfo prop, List<string> fieldList)
        {
            if (!D2Bk.IsNotInTable(prop))
            {
                fieldList.Add(bracketedAlias + "." + prop.Name + " AS " + alias + prop.Name);
            }
            if (prop.PropertyType == typeof(EntityCollection))
            {
                DontIncludeColumnsAttribute[] dontInclude = (DontIncludeColumnsAttribute[])
                                                            prop.GetCustomAttributes(typeof(DontIncludeColumnsAttribute), false);
                EntityCollection tpa = ((EntityCollection)prop.GetValue(entity, null));
                if (tpa != null)
                {
                    TypeContainedAttribute[] typeAttributes = (TypeContainedAttribute[])
                                                              prop.GetCustomAttributes(typeof(TypeContainedAttribute), false);
                    if (typeAttributes.Length == 0)
                    {
                        throw new EntityException("Please add the TypeContainedAttribute attribute to the property " + prop.Name);
                    }
                    TypeContainedAttribute typeAttribute = typeAttributes[0];
                    if (typeAttribute == null)
                        fieldList.AddRange(GetPrecacheFieldList(tpa.ElementInstance, dontInclude.Length == 0));
                    else
                        fieldList.AddRange(GetPrecacheFieldList(tpa.ElementInstance, prop.Name, dontInclude.Length == 0, precache));
                }
            }
            else
            {
                string computedColumn = D2Bk.GetComputedColumn(prop);
                if (!string.IsNullOrEmpty(computedColumn))
                    fieldList.Add(computedColumn + " AS " + alias + prop.Name);
            }
        }


        /// <summary>
        /// Provides the <CODE>ORDER BY</CODE> clause to support ordering of  the results. 
        /// </summary>
        /// <param name="sorts">sort criteria from the search criteria object</param>
        /// <returns>String with ORDER clause.</returns>
        private static string GetOrder(IEnumerator sorts)
        {
            StringBuilder order = null;
            if (!sorts.MoveNext())
            {
                return "";
            }
            do
            {
                String col = (String)sorts.Current;
                if (order == null)
                {
                    order = new StringBuilder();
                }
                else
                {
                    order.Append(", ");
                }
                order.Append(col);
            } while (sorts.MoveNext());
            return order.ToString();
        }


        /// <summary>
        /// Gets a field from inside the parenthesis of a function call
        /// </summary>
        /// <param name="fld">Function call text to extract the field</param>
        /// <returns>Field from Function</returns>
        private static string GetFieldFromFunction(string fld)
        {
            int iPosLeft = fld.LastIndexOf("(");
            if (iPosLeft < 0)
                return fld;
            int iPosRight = fld.IndexOf(")");
            if (iPosRight < 0)
                return fld;
            return fld.Substring(iPosLeft + 1, iPosRight - iPosLeft - 1);
        }


        /// <summary>
        /// Provides the <CODE>WHERE</CODE> clause to support _load.
        /// </summary>
        /// <param name="bindings">Bindings the search bindings from the search criteria object.</param>
        /// <returns>Strign with "where" clause.</returns>
        internal String GetWhere(IEnumerator bindings)
        {
            StringBuilder where = null;
            if (!bindings.MoveNext())
            {
                return "";
            }
            do
            {
                Clause bdg = (Clause)bindings.Current;
                object val = bdg.Value;
                String fld = bdg.Field;
                Operator opr = bdg.Operator;

                //Will have a value if correlated query.
                Type entityType = bdg.EntityType;

                if (where == null)
                {
                    where = new StringBuilder();
                }
                else
                {
                    where.Append(" " + bdg.Boolean + " ");
                }
                where.Append("\r\n");
                if (val is Filter)
                {
                    //Correlated queries
                    if (entityType != null)
                    {
                        if (((Filter)val).Projections().Count == 0)
                            ((Filter)val).Projections().Add(fld);
                        ((Filter)val).Distinct = true;
                        string correlated = GetLoadSQL(
                            (Filter)val, entityType);
                        where.Append(MapField(GetFieldFromFunction(fld)));
                        where.Append(" " + opr + " ( ");
                        where.Append(correlated);
                        where.Append(")");
                    }
                    else
                    {

                        Filter sc = (Filter)val;

                        where.Append("(");
                        where.Append(GetWhere(sc.Bindings()));
                        where.Append(")");
                    }
                }
                else
                {
                    if (fld != null)
                        where.Append(fld);
                    if (val == null)
                    {
                        where.Append(" " + Operator.IsNull);
                    }
                    else
                        if (bdg.Operator == Operator.IsNull ||
                            bdg.Operator == Operator.IsNotNull ||
                            bdg.Operator == Operator.NotIn ||
                            bdg.Operator.ToString() == "In")
                        {
                            where.Append(" " + bdg.Operator);
                            if (bdg.Operator.ToString() == "In" || bdg.Operator == Operator.NotIn)
                            {
                                bool addTicks = false;
                                PropertyInfo prop = TheObject.GetType().GetProperty(fld);
                                if (prop != null)
                                {
                                    TypeAttribute[] typeAttribute = (TypeAttribute[])prop.GetCustomAttributes(typeof(TypeAttribute), true);
                                    if (typeAttribute.Length > 0)
                                    {
                                        if (String.Compare(typeAttribute[0].TypeName.Name, "string", true) == 0)
                                        {
                                            addTicks = true;
                                        }
                                    }
                                    else
                                        if (prop.PropertyType == typeof(string))
                                        {
                                            addTicks = true;
                                        }
                                }
                                StringBuilder inValue = new StringBuilder(bdg.Value.ToString());
                                if (addTicks && !bdg.Value.ToString().StartsWith("'"))
                                {
                                    //Don't know if it is a good idea to remove the tickmarks.
                                    //inValue = inValue.Replace("'","");
                                    string[] values = bdg.Value.ToString().Split(',');
                                    inValue = new StringBuilder();
                                    for (int i = 0; i < values.Length; i++)
                                    {
                                        inValue.Append("'" + values[i] + "',");
                                    }
                                    inValue.Remove(inValue.Length - 1, 1);
                                }
                                where.Append("(" + inValue + ")");
                            }
                        }
                        else
                            if (fld != null)
                            {
                                where.Append(" " + bdg.Operator +
                                             D2BkConnection.ParameterName("@FLD" + D2Bk.d2BkSqlParameters.CurrentField.ToString(CultureInfo.InvariantCulture)));
                                D2Bk.d2BkSqlParameters.CurrentField++;
                            }
                            else
                            {
                                where.Append(string.Format("({0})", val));
                            }
                }
            } while (bindings.MoveNext());
            return where.ToString();
        }

        internal static List<string> GetJoinList(object entity)
        {
            return GetJoinList(entity, null);
        }

        /// <summary>
        /// Get join expression for entity.
        /// </summary>
        /// <returns></returns>
        internal static List<string> GetJoinList(object entity, DBJoin previousJoin)
        {
            List<string> fieldList = new List<string>();
            string dbBrackets = D2BkConnection.DBBrackets();
            foreach (PropertyInfo prop in (entity.GetType().GetProperties()))
            {
                if (prop.PropertyType == typeof(EntityCollection))
                {
                    EntityCollection tpa = ((EntityCollection)prop.GetValue(entity, null));
                    if (tpa != null)
                    {
                        TypeContainedAttribute typeAttribute = (TypeContainedAttribute)
                                                               prop.GetCustomAttributes(typeof(TypeContainedAttribute), false)[0];
                        string typeContainedInAlias = prop.Name;
                        if (tpa.ElementInstance.GetType().IsSubclassOf(typeof(Entity)))
                        {
                            //DBJoin join = ((Entity)tpa.ElementInstance).GetJoin(entity.GetType().Name);
                            DBJoin join = ((Entity)entity).GetJoin(typeContainedInAlias);
                            if (join != null)
                            {
                                StringBuilder joinLine = new StringBuilder();
                                //if (previousJoin == null)
                                //{
                                if (dbBrackets.Length > 1)
                                {
                                    joinLine.Append("\r\n " + join.JoinType() + " " +
                                                    D2BkConnection.DBOwner() + dbBrackets[0] + ((Entity)tpa.ElementInstance).TableName() + dbBrackets[1]);
                                    joinLine.Append(" " + D2BkConnection.DBTableAS() + " " + dbBrackets[0] + typeContainedInAlias + dbBrackets[1] + " ");
                                }
                                else
                                {
                                    joinLine.Append(" " + join.JoinType() + " " +
                                                    D2BkConnection.DBOwner() + ((Entity)tpa.ElementInstance).TableName());
                                    joinLine.Append(" " + D2BkConnection.DBTableAS() + " " + typeContainedInAlias + " ");
                                }
                                if (join.JoinType() != JoinTypeEnumeration.CROSS)
                                {
                                    joinLine.Append(" ON ");
                                    if (dbBrackets.Length > 1)
                                    {
                                        List<List<string>> dotSplit = new List<List<string>>();
                                        foreach (string equalSplitted in join.ToString().Split('='))
                                        {
                                            dotSplit.Add(new List<string>(equalSplitted.Split('.')));
                                        }

                                        joinLine.Append(string.Format("{0}{1}{2}.{3} = {0}{4}{2}.{5}",
                                                                      dbBrackets[0], dotSplit[0][0], dbBrackets[1],
                                                                      dotSplit[0][1].Trim(), dotSplit[1][0].Trim(), dotSplit[1][1]));
                                    }
                                    else
                                    {
                                        joinLine.Append(join.ToString());
                                    }
                                }
                                fieldList.Add(joinLine.ToString());
                            }
                            fieldList.AddRange(GetJoinList(tpa.ElementInstance, join));
                        }
                        else if (tpa.ElementInstance.GetType() == typeof(EntityCollection))
                        {
                            fieldList.AddRange(GetPrecacheFieldList(((EntityCollection)tpa.ElementInstance).ElementInstance));
                        }
                    }
                }
            }
            return fieldList;
        }


        internal string GetLoadSQL(Filter sc, Type parentType)
        {
            return GetLoadSQL(sc, (Entity)Activator.CreateInstance(parentType), 0, "");
        }

        internal string GetLoadSQL(Filter sc, Entity parent, string hint)
        {
            return GetLoadSQL(sc, parent, 0, hint);
        }
        /// <summary>
        /// Builds a <CODE>SELECT</CODE> statement that will perform a search using the search criteria.
        /// </summary>
        /// <param name="sc">Search criteria</param>
        /// <param name="parent">Instance of Entity class that is the parent for an eventual join or 
        /// the table for this select statement</param>
        /// <param name="currentField">Current field for parameter (necessary for correlated queries).</param>
        /// <param name="hint"></param>
        /// <returns>String with the D2Bk statement that performs the Select.</returns>
        internal string GetLoadSQL(Filter sc, Entity parent, int currentField, string hint)
        {

            try
            {
                new PermissionSet(PermissionState.Unrestricted).Assert();
                //StringBuilder to build and return the final SELECT statement
                StringBuilder sql = new StringBuilder("SELECT ");
                //Array of fields to be selected in the statement
                List<string> projections;
                //Strings to build WHERE and ORDER clauses
                string where, order;
                StringBuilder whereList = new StringBuilder("");
                StringBuilder orderList = new StringBuilder("");

                //Append DISTINCT clause if search criteria says so
                if (sc.Distinct)
                {
                    sql.Append(" DISTINCT ");
                }

                if (D2BkConnection.ProviderType().IndexOf("Oracle") < 0)
                {
                    if (sc.Top > 0)
                    {
                        sql.Append(" TOP  ");
                        sql.Append(sc.Top);
                        sql.Append("  ");
                    }
                }
                else
                {
                    if (sc.Top > 0)
                    {
                        sc.MaxRows = sc.Top;
                    }
                }
                if (sc.Projections() == null || sc.Projections().Count == 0)
                {
                    projections = GetPrecacheFieldList(parent);
                }
                else
                {
                    //projections = sc.Projections();
                    projections = GetPrecacheFieldList(parent, sc.Projections());
                }
                bool bNeedComma = false;
                foreach (object item in projections)
                {
                    string fld = (string)item;
                    sql.Append(bNeedComma ? ", " : "");
                    bNeedComma = true;
                    sql.Append(fld);
                }

                string brackets = D2BkConnection.DBBrackets();
                where = GetWhere(sc.Bindings());
                order = GetOrder(sc.Sorts());
                if (brackets.Length > 1)
                {
                    sql.Append(string.Format(" FROM {0}{4}{1}{5} {6} {4}{2}{5} {3} ", D2BkConnection.DBOwner(), parent.TableName(), parent.GetType().Name, hint, brackets[0], brackets[1], D2BkConnection.DBTableAS()));
                }
                else
                {
                    sql.Append(string.Format(" FROM {0}{1} {4} {2} {3} ", D2BkConnection.DBOwner(), parent.TableName(), parent.GetType().Name, hint, D2BkConnection.DBTableAS()));
                }

                List<string> joinList = GetJoinList(parent);

                foreach (string joinLine in joinList)
                {
                    whereList.Append(joinLine);
                }
                if (sc.Customs != null && sc.Customs != "")
                    whereList.Append(sc.Customs);

                if (where.Length > 0)
                {
                    whereList.Append(" WHERE ");
                    whereList.Append("(" + where + ")");
                }


                if (order.Length > 0)
                    orderList.Append(" ORDER BY " + order);

                sql.Append(whereList);
                sql.Append(orderList);
                return sql.ToString();
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw ex;
            }
            finally
            {
                CodeAccessPermission.RevertAssert();
            }
        }

        internal string GetFunctionSQL(string sqlFunction, Filter sc, Entity parent)
        {
            string[] projection = { sqlFunction };
            sc.AddProjection(projection);
            D2Bk.d2BkSqlParameters.CurrentField = 1;
            return GetLoadSQL(sc, parent, "");
        }

        /// <summary>
        /// Returns D2Bk to execute D2Bk function on rows.
        /// </summary>
        /// <param name="sqlFunction">Function name</param>
        /// <param name="sc">Search criteria</param>
        /// <returns></returns>
        internal string GetFunctionSQL(string sqlFunction, Filter sc)
        {
            string[] projection = { sqlFunction };
            sc.AddProjection(projection);
            D2Bk.d2BkSqlParameters.CurrentField = 1;
            return GetLoadSQL(sc, GetEntityType());
        }
        /// <summary>
        /// Method for building a <CODE>DELETE</CODE> statement that will 
        /// perform a search using the named search critieria.
        /// </summary>
        /// <param name="sc">Search criteria.</param>
        /// <returns>Array list with D2Bk and parameters.</returns>
        internal ArrayList GetDeleteSQL(Filter sc)
        {
            StringBuilder sql = new StringBuilder("DELETE ");
            ArrayList results = new ArrayList();
            string where;
            StringBuilder whereList = new StringBuilder("");

            sql.Append(D2BkConnection.DBOwner());
            sql.Append(GetPrimaryTable());

            D2Bk.d2BkSqlParameters.CurrentField = 1;
            where = GetWhere(sc.Bindings());
            D2Bk.d2BkSqlParameters.CurrentField = 1;
            if (where.Length > 0)
            {
                whereList.Append(" WHERE ");
                whereList.Append("(" + where + ")");
            }
            sql.Append(whereList);
            results.Insert(0, sql);
            return results;
        }



        /// <summary>
        /// 
        /// </summary>
        /// <param name="sc"></param>
        /// <returns></returns>
        /// <remarks>This is an example: <c>code</c>
        /// <code>
        /// 
        /// </code></remarks>
        internal string GetCountSQL(Filter sc)
        {
            sc.AddProjection(new string[] { "COUNT(*)" });
            return GetLoadSQL(sc, TheObject, 0, "").Insert(7, "COUNT(*) ");
        }


        /// <summary>
        /// Builds an Insert statement based on the object's properties.
        /// </summary>
        /// <param name="theObject">Object to extract Insert from</param>
        /// <returns>ArrayList - the 1st element is the D2Bk string. The others are each parameter value</returns>
        internal string GetInsertSQL(Entity theObject)
        {
            StringBuilder sFieldList = new StringBuilder("INSERT INTO " + D2BkConnection.DBOwner() + GetPrimaryTable() + " (");
            StringBuilder sValueList = new StringBuilder("  VALUES ( ");
            bool bNeedComma = false;
            foreach (PropertyInfo prop in (GetEntityType()).GetProperties())
            {
                try
                {
                    object value;
                    value = prop.GetValue(theObject, null);
                    if ((!D2Bk.IsNull(value) && value != null) ||
                        String.Compare(prop.Name, "SYS_CREATED", true, CultureInfo.InvariantCulture) == 0 ||
                        String.Compare(prop.Name, "ID_CREATED", true, CultureInfo.InvariantCulture) == 0)
                    {
                        string tableIdentityName = D2Bk.GetTableIdentityName(prop);
                        if (tableIdentityName.Length != 0)
                        {
                            if (bNeedComma)
                            {
                                sFieldList.Append(", ");
                                sValueList.Append(", ");
                            }
                            sFieldList.Append(prop.Name);
                            sValueList.Append(tableIdentityName);
                        }
                        else
                        {
                            //an Oracle Sequence is considered to be always a number. 
                            //if it is zero, it means it was not initialized (there is no Null for integers)
                            //in this case we get the value from the sequence.
                            //otherwise we get it from the property itself.
                            if (!D2Bk.IsColumnIdentity(prop) && D2Bk.HasSequenceName(prop) && int.Parse(value.ToString()) == 0 && !D2Bk.IsColumnZeroIsNull(prop))
                            {
                                if (bNeedComma)
                                {
                                    sFieldList.Append(", ");
                                    sValueList.Append(", ");
                                }
                                sFieldList.Append(prop.Name);
                                if (D2BkConnection.ProviderType().IndexOf("SQLite") >= 0)
                                    sValueList.Append(" Null ");
                                else
                                    sValueList.Append(D2Bk.GetSequenceName(prop));
                                bNeedComma = true;
                            }
                            else
                                if (!D2Bk.IsColumnIdentity(prop))
                                {
                                    if (!D2Bk.IsColumnDisplayOnly(prop) && !D2Bk.IsNotInTable(prop))
                                        if (!(D2Bk.IsColumnZeroIsNull(prop) && int.Parse(value.ToString()) == 0))
                                        {
                                            if (bNeedComma)
                                            {
                                                sFieldList.Append(", ");
                                                sValueList.Append(", ");
                                            }
                                            sFieldList.Append(prop.Name);
                                            sValueList.Append(D2BkConnection.ParameterName("@" + prop.Name));
                                            D2Bk.d2BkSqlParameters.CurrentField++;
                                            bNeedComma = true;
                                        }
                                }
                                else if (D2BkConnection.ProviderType().IndexOf("SQLite") >= 0)
                                {
                                    if (bNeedComma)
                                    {
                                        sFieldList.Append(", ");
                                        sValueList.Append(", ");
                                    }
                                    sFieldList.Append(prop.Name);
                                    sValueList.Append(" Null ");
                                    bNeedComma = true;
                                }
                        }
                    }
                }
                catch (Exception e)
                {
                    Log.Error("Exception at " + MethodBase.GetCurrentMethod().Name, e);
                    throw;
                }
            }
            sFieldList.Append(" )");
            sValueList.Append(" )");
            sFieldList.Append(sValueList.ToString());
            return sFieldList.ToString();
        }


        /// <summary>
        /// Build update D2Bk for updating based on search criteria.
        /// </summary>
        /// <param name="theObject">Entity template.</param>
        /// <param name="sc">Search criteria.</param>
        /// <param name="changedProperties"></param>
        /// <returns>String with D2Bk update statement.</returns>
        internal string GetUpdateSQL(Entity theObject, Filter sc, ArrayList changedProperties)
        {
            StringBuilder sql = new StringBuilder("UPDATE ");
            String where;
            Object value;
            StringBuilder whereList = new StringBuilder("");

            sql.Append(D2BkConnection.DBOwner());
            sql.Append(GetPrimaryTable());
            sql.Append(" SET ");
            bool bHasComma = true;

            try
            {
                new PermissionSet(PermissionState.Unrestricted).Assert();
                foreach (ArrayList arrChanged in changedProperties)
                {
                    try
                    {
                        string propName = (string)arrChanged[0];
                        PropertyInfo prop = GetEntityType().GetProperty(propName);
                        if (!D2Bk.IsColumnPrimaryKey(prop) && !D2Bk.IsColumnDisplayOnly(prop) && !D2Bk.IsNotInTable(prop))
                        {
                            string mappedFld = theObject.MapField(prop.Name);
                            try
                            {
                                if (String.Compare(prop.Name, "SYS_UPDATED", true) == 0)
                                {
                                    prop.SetValue(theObject, DateTime.Now, null);
                                }
                                if (String.Compare(prop.Name, "ID_UPDATED", true) == 0)
                                {
                                    prop.SetValue(theObject, D2Bk.CurrentUser(), null);
                                }
                                value = prop.GetValue(theObject, null);
                                //Will try to get the real value of the property's private value; this is
                                //necessary for cases where the property is going to be bound to a control and
                                //it may return String.Empty if the value is Null. However, the Null value is 
                                //necessary to build the where clause when updating the object to the database.
                                if (value != null)
                                {
                                    if (value.GetType() != typeof(EntityCollection))
                                    {
                                        FieldInfo fi;
                                        Type targetType = GetEntityType();
                                        do
                                        {
                                            fi = targetType.GetField("_" + prop.Name, BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.IgnoreCase);
                                            if (fi == null)
                                            {
                                                targetType = GetEntityType().BaseType;
                                            }
                                        } while (fi == null && targetType != typeof(object));
                                        if (fi != null)
                                        {
                                            object theValue = fi.GetValue(theObject);
                                            value = theValue;
                                        }
                                    }
                                }
                                Type propertyType;
                                object[] customAttributes = prop.GetCustomAttributes(typeof(TypeAttribute), false);
                                if (customAttributes.Length > 0)
                                    propertyType = ((TypeAttribute)customAttributes[0]).TypeName;
                                else
                                    propertyType = prop.PropertyType;
                                value = Entity.Parse(value, propertyType);
                                if (!D2Bk.IsColumnIdentity(prop))
                                {
                                    if (!bHasComma)
                                    {
                                        sql.Append(",  ");
                                    }
                                    bHasComma = false;
                                    sql.Append(mappedFld);
                                    sql.Append(" = ");
                                    sql.Append(D2BkConnection.ParameterName("@" + prop.Name));
                                    D2Bk.d2BkSqlParameters.CurrentField++;
                                }
                            }
                            catch (Exception e)
                            {
                                Log.Error("Exception at " + MethodBase.GetCurrentMethod().Name, e);
                                throw;
                            }
                        }
                    }
                    catch (Exception e)
                    {
                        Log.Error("Exception at " + MethodBase.GetCurrentMethod().Name, e);
                        throw;
                    }
                }
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                throw ex;
            }
            finally
            {
                CodeAccessPermission.RevertAssert();
            }
            D2Bk.d2BkSqlParameters.CurrentField = 1;
            where = GetWhere(sc.Bindings()); //, false);
            D2Bk.d2BkSqlParameters.CurrentField = 1;
            if (where.Length > 0)
            {
                whereList.Append(" WHERE ");
                whereList.Append("(" + where + ")");
            }
            sql.Append(whereList);
            return sql.ToString();
        }
    }
}